import pymysql

'''
 # 查询数据库版本
print ('win101机子上的mysql',"*"*8)
db=pymysql.connect("win101","root","root","mrsoft")
cursor=db.cursor()
cursor.execute("select version()")
data=cursor.fetchone()
print ("Database 版本号是： %s" % data)
db.close()


print ('\n查询mysql')
db=pymysql.connect("192.168.1.101","root","root","mrsoft")
cursor =db.cursor()
cursor.execute("drop table if exists books")
#查询表
sql ="""
create table books(
id int(8) not null auto_increment,
name varchar(50) not null,
category varchar(50) not null,
price decimal(10,2) default null,
publish_time date default null,
primary key (id)
) engine=myisam auto_increment=1 default charset=utf8;
"""
cursor.execute(sql)
db.close()
print ('建表完成\n')


print ('查询sql添加数据')
db=pymysql.connect("win101","root","root","mrsoft")
cursor=db.cursor()
data=[("逐浪软件","python",'79.8','2018-5-20'),
      ("从零开始学python",'python','69.80','2018-6-18'),
      ("PHP项目开发实战",'php','79.8','2016-5-21'),
      ("dotNET战略起步",'net','79.8','2016-5-21'),
      ("net微软大开发",'net','79.8','2016-5-21')
       ]
try:
    #执行sql语句，插入多条数据
    cursor.executemany("insert into books(name,category,price,publish_time) values (%s,%s,%s,%s)",data)
    #提交数据
    db.commit()
    print ('查询添加完成\n')
except:
    #发生错误时回滚
    db.rollback()

db.close()    

'''

print ('sql查询数据')
db=pymysql.connect("win101","root","root","mrsoft")
cursor=db.cursor()
#执行sql语句
cursor.execute("select * from books")
result= cursor.fetchall()

print(result,"\n")

for book in result:
    print("图书：《{name}》,价格：{price}元,分类：{aa}".format(name=book[1],price=book[3],aa=book[2]))
db.close()    
